Copyright: Copyright (c) 1996 Macro Systems.


Contents

About The Spreadsheet Assistant™

Other Macro Systems Products

How To Order

Installation

The Toolbars

The Menu Additions

The Commands

The Commands Added to the Excel Menus

The QuickAccess Menu

Customizing

Questions and Answers

What's New

The Legal Stuff

Feedback / Technical Support

Custom Macros/About Macro Systems


About The Spreadsheet Assistant
About The Spreadsheet Assistant™

The Spreadsheet Assistant™ is designed to save you time in your use of Microsoft Excel by eliminating repetitious tasks.  The new commands will increase your productivity every time you are in Excel.  The commands will save you anywhere from a few seconds to a few minutes each time you use them.  You do not have to be an expert to use them.  They are designed for use by everyone.

There are over 150 new commands available from The Spreadsheet Assistant™.  The commands range from the simple to the very very powerful.  All are designed for ease of use.  There is more to the Spreadsheet Assistant™ than the list of commands that appears when you click on the Spreadsheet Assistant button.  The Spreadsheet Assistant™ adds commands to the Excel menus found at the top of the screen, and to the pop up menus when you press the right mouse button.  These pop up additions you will find very useful in developing spreadsheets.

By clicking on the Spreadsheet Assistant™'s button [image] or by selecting "The Spreadsheet Assistant™" from the Tool menu, you can see a list of almost all the commands.  You can access many of the commands directly from the Excel Menus.  Also, additional commands have been added to the Excel pop-up menus that are not listed on the command list.  If you want, you can use the option in The Spreadsheet Assistant™ to remove the menu additions.  And you can hide The Spreadsheet Assistant™'s toolbars by using an option on either the View menu or on the command list.

Another nice feature is The Spreadsheet Assistant™'s repeat button  [image]  If you run a command from the command list, this button is automatically customized to repeat the command!  And, if you highlight the button, it reminds you what the button is set to do.  Just another way to save you time!   Furthermore, you can assign any of the commands to buttons or let The Spreadsheet Assistant™ assign them to a button for you!

Please let me know what you think of The Spreadsheet Assistant™ and any improvements that are needed.  I use The Spreadsheet Assistant™ every day and look forward to hearing your comments.  And, If you think of a command you would like to have added, let me know.  We might be able to add it for you.

Sincerely, Bob Flanagan, Macro Systems


The Commands

Relocate Commands

Copy and Paste (Fill) Commands

Select Commands

Special Edit Commands

Time Period Commands

Multiplying, Dividing, Adding, and Subtracting

Converting Trailing Negatives to Leading Negatives

Data Commands

View and Window Commands

File Commands

Miscellaneous Commands

Print Commands

Unlisted Commands


Relocate Commands

The relocate commands are very useful.  They help you position the screen to display the cells that you want to see with a minimum of keystrokes and mouse clicks.  And they allow you to flash back to any location you mark.

Relocate Current Cell to Upper Left Corner of the Screen

Relocate Using Last Relocate Location

Store cell location for each return

Return to stored location

The last two commands above works even if you go to a different sheet or workbook.  (In fact, there are two sets of these commands so you can toggle between locations).  Its a great one to use when you are debugging a spreadsheet and need to go back and forth from one spot to another.  You will find these commands under the View menu.


The Copy and Paste (Fill) Commands
Copy and Paste (Fill) Commands

The following are the primary copy and paste commands:

Fill Highlighted Cells to the Right

Fill Highlighted Cells ALL the Way to the Right

Fill Highlighted Cells Down

 	Fill Highlighted Cells ALL the Way Down

Set the Target Column/Row

Fill to the Target Row

Fill to the Target Column

These commands first copy the selected cells and then pastes them either down or to the right.  And they return the screen right back to where it was before you issued the command.  No more having to scroll left and right or up and down to do your copy and paste!

If you use the command, Fill Highlighted Cells ALL the Way Down, then you may want to use the command Blank Rows Based On Reference Column to remove unneeded entries that may result.


Select Commands

These commands help you select text so that you can copy, paste, cut, format, etc.  These  commands are:

Select Cells to the Right of the Selected Range

Select All Cells to the Right of the Selected Range

Select Cells Below the Selected Range

Select All Cells Below the Selected Range

One advantage of these select commands is that the screen will almost never move during the selection process.  (There are some unique situations with window splits and freeze panes where there is a little movement.  However, these situations are very rare.)

The following commands allows you to control the cells that are selected.

Set the Target Column/Row

Select to the Target Column

Select to the Target Row

Using the select to the target column or row commands is useful if there is data in the cells following the data you are selecting, and you don't want to select those cells.  For example, you may have 12 months of data and then a SUM function in the next cell.  With these commands, you can select the 12 cells of data but not the sum cell.

On the last two commands listed above, you must use the first command (set target column/row) before using the other two commands.  Otherwise, the Spreadsheet Assistant doesn't know what your target column or row is.  It will let you know to run the set the target command if you forget!


Special Edit Commands

Format Commands

Colorization Commands

Blank Cells Based On Reference Column

Blank Cells Based On Reference Row

Insert a Big Dot

Insert a Thick Dash

Insert a Thick Underline

Insert a Long Thick Underline

Insert Blank Rows Every X Rows

Insert Blank Rows If Cells Are Different

Remove Blank Rows

Outlines and Boxes

Capitalization

Indenting and Truncating

Removing Un-needed Spaces

Fix (text flow) A Paragraph

Shift Macro Equations 5 Spaces to the Right

Storing and Writing Stored Text

Blank Entries

Transpose Rows and Columns

Copy Column Widths

Color Cells Based on IF Test

Shift Cell Format

Copy Formulas or All But Borders


Time Period Commands

There are four commands related to time in Bob's Macros.  They are:

Months Down or Across

Insert Date, Time, File Name and Sheet Name

Alarms

Activity Timers


Multiplying, Dividing, Adding, and Subtracting

To use the following commands, just highlight the selected range and select the command.  The command will add the necessary code to the contents of the cell and not modify any equations that are in the cell.  For example if the cell equation is "=A7*D8" and you select divide by 1000, the resulting cell equation is "=(A7*D8)/1000".

The following are the commands:

Perform Any Math Action

Divide By 2.2046 (Lbs to Kg)

Divide All By 10

Divide All By 100

Divide All By 1000

Multiply By 2.2046 (Kg to Lbs)

Multiply All By 10

Multiply All By 100

Multiply All By 1000

The Perform Any Math Action is an extremely powerful command.  Not only can you enter a math action like "/ 100" or "+ 50", you can also cell references instead of numbers.  For example, you can use "/ F5"  to have this command modify formulas so that they are divided by the contents of cell F5.

You can also use what is called R1C1 notation instead of A1 notation.  For example, if you enter "/ R[3]C[], then when a cell is modified, it is divided by the cell three rows below the cell and in the same column.  Negative numbers are allowed.  Do not use zero.  If you use R1C1 notation, you must use brackets.  The number inside the bracket is the distance in rows or columns from the cell being modified.  If there is no entry inside the brackets, then this means the same row or column.


Data Commands

 	The following commands will help you to manipulate your data and rearrange your cells.

Compare Two Columns - Stop When Not Alike

Compare Cells, Colorize Duplicate Entries

Re-arrange Range Based on Reference Column W/O Changing References

Re-arrange Range Based on Reference Row W/O Changing References

Load Data From One File to Another

Count The Entries In A Selection

Pivot Table Commands


View and Window Commands

The following commands are pretty obvious.  You will find them also on the Excel menus.

Zoom to 85%

Set Zoom Back to Normal (100%)

Cascade Screens (on the Excel menus, but easier to get to)

Click on the following commands to see detail on how they work:

Set All of the Worksheet's Windows to the Same Upper Left Cell and Zoom

Set All Selected Sheets to the Same Upper Left Cell and Zoom

Set all Selected Sheets to the Same Split and Freeze Panes

Zoom In

Zoom Out

Synchronize Commands


File Commands

Click onthe following for detail on these commands:

Create an ASCII File

Delete the Active File

Favorite Directories

Save All Files

Close All Files

Hide And Save The Active File

Rename The Active File

Return Path And Change Directory


Format Commands

The following commands don't really require much explanation.  Basically, they avoid having to click the right mouse button (or Format from the menu) from the menu), selecting format cells, picking a tab, selecting a format from a big list, etc.

Format to 0

Format to 0.0

Format to 0.00

Format to 0.000

Format to 0%

Format to 0.0%

 	The default setting on the above is to use commas in the first four commands.  However, you can toggle use of commas off and on by a command in the command list.  This command is just above the list of the above commands.


Miscellaneous Commands

Determine the Width of the Selected Range

Determine the Location and Length of the Longest Text Entry

Setting and Using Alarms

Activity Timers

Initialize a Sheet

Sticky Notes

Insert a Circle

Repeat Find Command

Flip Calculation Between Automatic and Manual

Find A Macro Command

Display Macro Command Name

Remove or Show The New Menu Commands

Print the Command List

Round to 10s, 100s, etc or UnRound

Formating Numbers based on an IF test

Select Cells with Numbers Only

Cell Protection Macros

UnHide the Column To The Right

Hide and Protect A File

Hide The Entire Row If Entry In Cell

Add or Remove Page Breaks Based On Cell Entry

The Loan Calculator

Tool Bar And Tool Tip Editor

Display Range Information

Toggle View Formulas Off And On

Export A Report To A XLS File


Print Commands

Hopefully, the following commands are self explanatory.

Display Bottom of Print Area (F8 then allows extension)

Add or Subtract Rows or Columns to the Print Area

Shift Print Area "x" Rows or Columns

Print Selection and Then Restore Print Area

Store the Print Range and/or Titles For Use On Another Sheet

Apply the Stored Print Range to the Current Sheet

Set the Print Range On All Selected Sheets to the Current Selected Range

Insert Horizontal Page Breaks Every X Rows

Remove the Print Area, Titles, Page Breaks...

Set Print Titles

The following panel appears when the Remove the Print Area,... command is selected:

Please note the option on this panel to remove manual page breaks removes ALL page breaks on the sheet, and is not limited by the range you may have selected on the sheet.  If you want to remove the manual page breaks that cover just a range of rows, select a single column of blank cells and use the command "Set Page Break If An Entry In Cell, Remove If No Entry".

The option to remove automatic page breaks is a display option.  If you select it, and then decide you want to see the automatic page breaks, you will need to manually reset this option.  Do so under Tools, Options, View tab.


Unlisted Commands

There are several commands found on the pop-up menus that are not listed when the command list is displayed.   For example, there are clear options on the cell pop-up menus, and to the row and column pop-up menus.


Customizing

There are several commands that allow you to customize The Spreadsheet Assistant to your specifications.  The following are the customization options built into the Spreadsheet Assistant.  In addition, you can customize Microsoft Excel's menus by using the Menu Editor.

Using The Menu Editor To Customize The Excel Menus

Show/Hide The Spreadsheet Assistant ToolBars

 	This command gives you the option to either show the Spreadsheet Assistant's toolbars  or prevent the toolbars from appearing when you load Excel.  This command is found under the View menu and on the command list.  If you hide the toolbars, you can still get to the commands by using the command "The Spreadsheet Assistant" located on the Tool menu.

Adding Commands to Buttons

With Microsoft Excel, you can add buttons to your toolbars or create new toolbars.  And, you can assign any of the Spreadsheet Assistant's commands to the buttons.  This makes it much more convenient to use the commands that help you the most.  The easiest way to assign a command to a button is to first select the option below the list of commands titled "assign to button".  Then select the command and click on the OK button.  A selection list of four buttons will appear.  Pick the one of your choice.  The Spreadsheet Assistant then creates a single button toolbar with the name of the command assigned to the button and to the toolbar.

You can easily customize the image of the button.  The instructions on how to do so appear whenever you assign a command to a button.

You can also assign any of the commands to a button manually or run manually.  This second approach allows you to incorporate the Spreadsheet Assistant's commands in your own macro.  To manually assign to a button, first use the command on the list that displays the macros names associated with the commands to find out the exact spelling of the macro you are interested in.  The next step is to right click on any of the toolbars, and select customize.  Select the button you want to use from the selections that Excel presents and drop it on a toolbar or out in the open.  If the button is one that is in the custom group, you will be prompted for the macro name.  If it is one from the standard groups, Excel will assume you want the predefined function to be used.  However, you can change it by right clicking on the button and assigning a macro to it.

Adding Or Removing the Spreadsheet Assistant Menu Additions

The default setting of The Spreadsheet Assistant is to add a number of commands to the Excel Menus and to add the menu menu titled "Quik".  If you decide you don't want the commands added or the Quik menu, you can selectively remove groups of commands by using the command"Add or Remove Commands from Excel's Menus" found near the bottom of the command list.


The Toolbars

The Spreadsheet Assistant™ comes with two toolbars.  The two toolbars are called The Spreadsheet Assistant™ Toolbar and the Zoom Toolbar.  You shold make room in the docking area for toolbars at the top of Excel for these toolbars.  If you don't have room, you can remove tool buttons that you don't need.

To remove buttons, select View, Toolbars, Customize.  Then click and drag off buttons you do not need.  If you just click and hold on a button, the purpose of the button will be displayed at the bottom of the customize box.

To move the Spreadsheet Assistant™ toolbars up to the docking area, just click on the top of the toolbars and drag them to the docked toolbar area.  If you want, you can leave them as floating toolbars or if you drag them to the docked area and there is no room, they will be added to the docked area and expand the rows of docked toolbars.  For example, you can remove the zoom button that Excel provides to make room for the Zoom toolbar.

If you don't want to use the toolbars, use the Hide/Show Spreadsheet Assistant™ Toolbars menu choice under the View menu to permanently hide the toolbars.  Otherwise, the toolbars will reappear each Excel session.  If at a later date you decide you want to use the toolbars, use this same command to re-display the toolbars.

The following are the buttons on The Spreadsheet Assistant™ toolbar:

  This button displays a list of the commands.  You have the option to either execute the command, assign it to a button for quick access, or display the help information on the command.

  This button repeats the last command that was selected from the list of commands.

The following are the buttons on the Zoom Toolbar:

  This button increases the zoom percentage 5%.  Using it and its companion button to quickly adjust the zoom setting to what you want.

  This button decreases the zoom percentage 5%.


Installation

Installation of The Spreadsheet Assistant™ is very easy.

STEP 1

Close Excel if it is open.

STEP 2

Copy the file ASSISTNT.XLS to your Excel startup directory. The startup directory is almost always named "XLSTART" and is almost always a subdirectory of your Excel directory.  The following are examples of Excel startup directories:

C:\EXCEL\XLSTART

C:\MSOFFICE\EXCEL\XLSTART

By putting this file in your startup directory, it is loaded each time you load Excel.  This is extremely important, as The Spreadsheet Assistant™ is designed to help you all the time while you are in Excel.  And if it is not loaded when you need it, you won't get as much benefit.

In case you are wondering, yes you can put the file in a directory other than your startup directory.  But, we don't recommend that since the Excel menus will not reflect the menu additions until ASSISTNT.XLS is loaded.  If you do put the file in a directory other than your startup directory, you will need to manually load the file.

If you do not have an XLSTART directory under your EXCEL directory, just create one (name it ALTSTART). It should be a subdirectory of your EXCEL directory.  To tell Excel that this is a startup directory, start Excel, go to Options under the Tools menu, and select the General tab.  Fill in the full path and name of the alternate startup directory.  For example: "C:\EXCEL\ALTSTART".

STEP 3

Copy the file ASSISTNT.HLP to your Windows directory. If this file is not in the Windows directory, the option to display help information will not work. DO NOT copy it to your XLSTART directory.

STEP 4

Start up Excel!  When ASSISTNT.XLS is opened, you will see a message appear for a few seconds at the bottom of the screen.  And, you will see new menu items under all the menus.

If for some reason Excel doesn't open ASSISTNT.XLS when you start Excel, select TOOLS, OPTIONS, GENERAL and specify the directory containing ASSISTNT.XLS as your alternate startup directory.  Then close and re-open Excel.  If you properly specified the alternate directory, ASSISTNT.XLS will open.

Please note that ASSISTNT.XLS is a hidden file.  You can tell it was opened by the new menu additions and the two new toolbars that appear.


Relocate Current Cell

This command relocates the active cell to the upper left corner of the window.  This is a quick way to reposition the cells on the screen.  If, after you've used this command you've scrolled around in the spreadsheet, you can return to where you were by using the command

Relocate Using Last Relocate Location


Relocate using Last Relocate

This command becomes active after you have used the command "Relocate Current Cell to Upper left Corner of the screen".  It allows you to repeat the results of the this command.  That is to say, it relocates you back to that cell and sets the screen accordingly.

Please note that if you have added or deleted rows or columns above or to the left of the stored location, then you will be returned to a location off by the number of rows or columns added or deleted.  Although we could have defined a range name on your sheet to prevent that, we felt that was a liberty we should not take with your spreadsheet.  Thus we store the row and column numbers and return to that position.


Blank Cells Based On Reference Column

If you used the command

Fill Highlighted Cells ALL the Way Down,

then you will probably end up with cells that shouldn't have entries.  For example if you have skip rows to improve readability, then those skipped rows will have had cells copied into them.  That is where this command helps.

First,  select the range of cells (multiple columns allowed) that need cells blanked or erased.  Then run this command (Blank Cells Based on Reference Column).  You are first asked to specify a cell in the reference column.  You are then asked if you want to copy text found in the reference column.  Reply yes if you want to copy dashes that have been put in as underlines.  Once this is done, the command takes over.  It compares the selected cells to the cells in the reference column.  If the cell entry in the reference column is blank, then the cell in the selected range is erased.  if it contains a text entry and you said yest to copy text entries, the text is copied (even it is not an underline).


Insert a Thick Underline

If you like to uses dashes (------) in a cell to create an underline affect, you'll really like this macro.  It uses the Wide Latin font which comes with Windows to create a very thick underline to create a much better looking underline.   The difference between a Thick Underline and a Long Thick Underline is the length of the underline!

The following illustrates the difference between ordinary underlines and thick underlines:

Please note that if you use this command and then insert a row right below the cells containing the thick underline, the new cells below the underline will have the Wide Latin font.


Insert a Thick Dash

If you use Excel to create presentations or documentation, then you may like to put a dashin the cells to the left of your text.  As opposed to using the wimpy dash (-), this macro gives you a nice thick dash by using the Wide Latin font.

The following illustrates this:

 	Please note that if you use this command and then insert a row right below the cells containing the thick underline, the new cells below the dash will have the Wide Latin font.


Insert Blank Rows Every X Rows

Ever have a bunch of data and decide that you need to insert a blank row every couple of rows?  And then decide after you are half way through that you've spend a lot of time inserting rows?  Well, this command saves you that time.  Just highlight the area that needs the blank rows and let this command do the work.  What's more you have the option to shift the cells in the selected range down vs inserting an entire row.  But, if inserting an entire row is OK, then your selected range needs to be only one column wide!  And, you can specify how many blank rows are inserted.

Now, lets say you've run the command and done some work in your spreadsheet and deleted and added rows.  Your data is no longer nicely separated every X rows.  No problem.  Use the command Remove Blank Rows to remove the blank rows.  Then re-run this command to re-insert blank rows!


Remove Blank Rows

Use this command to remove unwanted blank rows in your data.  You have the option of deleting the entire row or shifting text below the blank cells upward.  If you want to delete the entire row, then all you need to do is to select a range that is a single column wide.


Outlines and Boxes

Although Excel's outline button has a lot of outlining and boxing options, it doesn't have them all.  So the Spreadsheet Assistant added the ones that were missing!

To use, select the range you want outlined or boxed and let the commands do the work.

A special word about the Remove Just the Outline command.  It does just that.  Cells which are boxed are not affected unless they are on the edge of the outline.  So, if you outline your work and realize that you need to remove the outline, just highlight the selection inside the outline and let the command remove the outline.  (Excel's option to remove lines removes all lines, not just the outline) .

The following is a list of the outline and box commands:

Outline using a dotted line

Outline using a thin line

Outline using a medium line

Outline using a thick line

Box using a dotted line

Box using a thin line

Box using a medium line

Box using a thick line

Remove just the outline


Capitalization

There are four commands that control capitalization:

 Convert All Letters to Lower Case

Capitalize the First Letter of the First Word Only

Capitalize the First Letter of All Words (also called proper case)

Capitalize All Letters

They do just what they say they do.  Just select the range and run the command of your choice.  They are available from both the command list and also Excel's Format menu.


Indent or Truncate Text

This command will add as many spaces as you want in front of text or will truncate the text the requested number of characters.  Please note that the truncate command does not truncate just spaces, but also letters, periods, etc.

The following is the box that appears that allows you to specify how much indentation or truncation is to occur:

These command acts only on cells containing text.  Numeric cells (numbers or formulas) are not affected.


Fix (text flow) A Paragraph

Using Excel as a mini word processor to write document is not too easy.  There is a command buried on one of the menus that helps flow paragraphs together.  But it removes the spaces at the beginning of a paragraph.  This command solves that - it retains the beginning indentation.  And, it is easy to find as it is on the Excel Edit menu.

The only restriction is that you must do one paragraph at a time.  Which is the safe way to do the command.

To use the command highlight the range that you want to fit the paragraph into before running the command.  Include extra blank lines to insure that there is enough space for the paragraph once it is fixed.  The command will flow the text so that it fits in the column range specified.  Be careful you don't select a range one column wide, or else you will end up with a very narrow paragraph! (In this situation, the command does confirm that you wanted to highlight a single column.)

In very rare situations, the indentation will be lost and the letter "Z" will appear instead.  This is an Excel bug.  To solve, just close and re-open Excel.


Storing and Writing Stored Text

Every now and then you may have to type the same text over and over again. To save time use these commands instead.  One stores the text that you want typed, and the other one writes it out.  These commands used in conjunction with the Repeat Button makes writing repetitious phrases very very easy.

The names of the two commands are:

Store Text to be Written Out by Write Out Stored Text Command

Write out Stored Text

Use the first command to specify the text to be written out and the options.  Once you have specified the text and options, you are asked if you want to run the write out stored text command.  If you want to run it additional times, you do not have to re-store out the text.

The Write Out Stored Text has many options as illustrated by the following panel that appears when it is selected:


Blank Entries

The following two commands in the Spreadsheet Assistant either blank duplicate entries and or fill blanks with the entry above.  The commands are:

Blank Entries If the Same As the Cell Above

Fill Blanks With the Contents of the Cell Above

The first creates blanks and the other fills blanks with the entry above.  They are found not only on the command list, but also under the Data menu.

How are these commands useful?  If you import data into Excel from external files then you may want to fill all the cells so that you can then sort the data.  Or you may want to remove all the duplicate entries to make the output reports look less cluttered.


Mark and Return

There are two commands that help you remember where you were and return you to that location when you're ready:

Store Current Cell Location For Easy Return

Return to Stored Location - From Any Spreadsheet

The first command stores the current cell and spreadsheet location.  The second returns you to that location from where ever you may be.

There are two related commands that also may prove useful:

Relocate Current Cell to Upper Left Corner of the Screen

Relocate Using Last Relocate


Compare Two Columns

How often do you have two columns of data and would like to find out where they are different?  That's what this command is designed to do for you.

The command asks you to select the cells in the first column and the first cell in the second column.  The columns can be on the same sheet or on different sheets in different worksheets.  When a difference is detected, a menu is displayed that shows the different cells and asks you if you want to highlight the cells.  You have the option to outline or turn the background red.  Next, you are asked if you wish to continue the comparison.  This continues until the data has been checked or you decide to stop.


Transpose Rows or Columns
Transpose Rows and Columns

This command takes a block of cells and transposes it (rows to columns or columns to rows) without changing references to the cells.  Also, the formulas inside the cells being transformed are not affected.  Excel has a transpose feature under the paste special menu command, but it works only if you are copying cells.  This command is the equivalent to that, but is a cut and transpose command, which is not available via Excel.  Formulas referring to the cells being transposed continue to refer to the transposed cells in their new locations.

For example, this command would convert

into

To run the command, first select the block of cells to be transposed and then select the command.  The command asks you where you want the transposed cells located.  It must be on the same sheet as the highlighted range.  You can not cross sheets with this command.  Finally, the command asks you what cell you want to appear in the top left once the work is done.

Be sure not to specify a location for the transposed data that is part of the un-transposed data.  To avoid this problem, make the destination for the transposed data an unused section of the sheet, then cut and past the transposed data where you really want it.


Re-arrange based on Reference Col
Rearrange based on Reference Col

The Excel sort command works great but it has one drawback:  Formulas that refer to cells inside the sorted range don't change the cells they refer to when the cells are sorted even though the contents move.  That's great most of the time, but sometimes you want to re-arrange the data in a sorted order and maintain the references.  Up until now, the only solution was manually cutting and pasting, cutting and pasting,....

To use this command, first select the range of data you want rearranged.  Then execute the command.  It then asks you to identify which column is used to sort the data.  It also asks you if you want the data rearranged in ascending or descending order.  Then it goes to work.  Please note that this command does a lot of work, so have patience.

If you want to re-arrange a range of data based on several columns, run the command several times, but select the columns in reverse order.  For example if you want the data rearranged by columns A, B, and C.  Run cby olumn C first, then B, then A.


Set All Windows the Same

Basically, if you have two or more windows on the same sheet, this command will set them up so that the same cell is in the upper left and the zoom setting is the same.  This makes it very easy to compare sheets, since you're looking at the same range of cells.


Same Upper Left Cell and Zoom

This command is located in the command list and is called:

Set All Selected Sheets to the Same Upper Left Cell and Zoom

It is also located under the View pull down menu as:

Set All Selected Sheets to the Same View

The command does just what its title describes.  When executed, it asks you to specify the cell to be located in the upper left, then it sets all selected sheets to have this cell in the upper left.  It also sets the zoom the same on all selected sheets.  This command will automatically skip non-worksheet sheets if you do a select charts by accident.  If you do not select any worksheets, it assumes that you want it to select all worksheets.

If you have created separate panes and frozen them, pick a cell in the main pane when you run this command.


Same Split and Freeze Panes

This command (Set All Selected Sheets to the Same Split and Freeze Panes) is a quick and easy way to get the same view (including the frozen panes) on a group of selected sheets.  You will be prompted for the rows and columns to be used to create the panes.  Once you've used this command, you should use the following related command to set selected sheets to the same view when you need to vs re-using this command (it avoids a little work on your part)

Set All Selected Sheets to the Same Upper Left Cell and Zoom


Zoom In and Out

The Zoom In and Out commands increase or decrease the zoom 5% each time they are run.  They are also associated with  the following buttons on the Zoom Tool bar:

 and

Increasing or decreasing the zoom in 5% increments is much more effective at getting the zoom you want versus using the default Zoom settings from Excel's magnifying glass icons.  And, the zoom changes almost as fast as you can click on the buttons.  If you've lost track of the zoom percentage, or just want to set the zoom to 100%, use the menu item added to the View menu to do so (or find it in the command list).


Synchronize Commands

There are four synchronize windows commands in The Spreadsheet Assistant.  Excel 4 used to have these commands, but they disappeared in Excel 5.  Well, they're back!  Please note that synchronization applies only to windows on the same sheet.  It does not work on two different sheets.  When the windows on the same sheet are synchronized, the word "sync" appears in the windows title bar.  Synchronization is very useful if you are checking data that is widely separated on a sheet, but you need to keep the same rows or columns in view.

The commands which hopefully are self explanatory are:

Horizontally Synchronizes the Current Sheet's Windows

Vertically Synchronizes the Current Sheet's Windows

Horizontally and Vertically Synchronize the Current Sheet's Windows

Removes Synchronization From Current Sheet


Reference Row Re-arrange
Reference Row Rearrange

I'm certain you are familiar with the Excel sort command.  It works great but it has one drawback:  Formulas that refer to cells inside the sorted range don't change the cells they refer to when the cells are sorted even though the contents move.  That's great most of the time, but sometimes you want to re-arrange the data in a sorted row order and maintain the formula references to the data as it is rearranged.  Up until now, the only solution was manually cutting and pasting, cutting and pasting,....

To use this command, first select the range of data you want rearranged.  Then execute the command.  It then asks you to identify which row is used to sort the data.  It also asks you if you want the data re-arranged in ascending or descending order.  Then it goes to work.  Please note that this command does a lot of work, so have patience.

If you want to rearrange a range of data based on several rows, run the command several times, but select the rows in reverse order.  For example if you want the data rearranged by row 1, 2 and 3, run the macro first on row 3, then row 2 and last on row 1.


Create an ASCII File

One of the hardest things to do in Excel is to create an ASCII file.  ASCII files are a very useful way to send data to another program or to a mainframe.  To create an ASCII file manually, you've got to remember which of the file Save As commands to use, and then figure out how to get header rows and columns into the output.  Kind of difficult if the header rows and columns are not adjacent to the main body of text you want to write out as an ASCII file.  Never fear, The Spreadsheet Assistant solves this one!

One word of warning.  If you want almost WYSIWYG results in your ASCII file, then you should use a Courier 10 point font.  If you use a proportional font like Ariel or Times Roman, weird results and truncation can happen.  Although Courier 10 isn't perfect, its the closest there is.  If the text strings are long, you may want to use Courier 8 point font.

When you execute the command, this command prompts you for the information it needs:  the main body, the header rows and columns, and the file name and location for the ASCII file.  If you don't supply a filetype with the ASCII file, then it uses a type of PRN as the default filetype.

One restriction on filenames - In Excel 5 you can not replace an existing file.  You must create a new file vs replacing an old one.  This appears to be an Excel 5 restriction (read as "bug" or "feature"; your choice).  However, in Excel 7, you can replace an existing file.

Please note that when you create an ASCII file, if you have text that extends past the border of the last column, all of that text will appear in the ASCII file.  So your output may be a little wider than you expect.

Related Commands:

Determine the Width of the Selected Range

Determine the Location and Length of the Longest Text Entry


Determine the Width

This command's primary purpose is to determine the width of a group of columns so that when you are creating an ASCII file, you know how wide the results will be.  To use, select a range of cells (it doesn't have to be the full columns) and run the command.  It tells you both the "true width", which is what the Excel widths add up to, and the "truncated width" which is what you see in an ASCII file.

Please note that when you create an ASCII file, if you have text that extends past the border of the last column, all of that text will appear in the ASCII file.  So your output may be a little wider than you expect.


Longest Text Entry

This is another command designed to help you with using Excel for writing documentation or creating ASCII files.  The full name of the command is:

Determine the Location and Length of the Longest Text Entry

To use, first highlight the range you want searched and let the command run.  When it completes running, it tells you which cell has the longest entry and how long the entry is.


Setting and Using Alarms

The Spreadsheet Assistant allows you to set up to ten alarms.  Each alarm can be set to go off at a specify time or to go off in X minutes.  Whichever you prefer.

This command is located in the command list and also under the Quick Access menu.  The following box appears when this command is selected:

Then, after selecting New, the following box appears so that you can set an alarm.

Please note that if you set an alarm and then close down Excel, the alarms are canceled and no warning of that is given.


Initialize a Sheet

When you create a new sheet or a new workbook, there may be certain characteristics that you like to set on the sheets.  For example, you may like to turn the grid off, use the Lotus transition formula entry and evaluation option, or get rid of the print header and footers.  Thus this command was born.  You have the option to set any or all of these options when you use this command.  If you want to make your settings in this command's dialog box permanent, use the save the Spreadsheet Assistant settings option.

This command works for worksheets or graph sheets only.


Find A Macro Command
Find A Command

With all the commands on the list, it became inevitable that a command was  needed to find a command!  Thus this command was created.  To use, you need to remember a key word, or better yet, a key phrase in the command's description.  The Excel find box will pop up and allow you to enter in the key word and then cycle through any matches that it finds.  Once you find the command you are after, close or exit the find box.  The command that is last found will automatically be displayed for execution when you exit the find command.


Display Macro Command Name

The command list for The Spreadsheet Assistant gives you a description of the commands, but doesn't tell you the exact macro names.  You really only need to know the exact macro name if you want to manually assign one of the commands to a button on a toolbar.  This command allows you to select the command you're interested in, and then it displays the macro name.

You will notice that all the macro names begin with "Z".  The reason for this is simple.  We didn't want to mix up the macros from the Spreadsheet Assistant with ones you may have already written.  You'll still see macros added by the Spreadsheet Assistant, but they will be on the bottom of the list, not mixed in with your own macros, unless your macros also begin with the letter "Z".


Feedback / Technical Support

 	All feedback, both positive and negative is welcome.  Macro Systems may be contacted by

postal mail:

Macro Systems

1008 Lawndale Road

Wilmington, DE 19810

E-mail:  72774.416@CompuServe.Com

phone:  302-475-0142

fax:  302-475-0142 (yes, its the same number)


Custom Macros/About Macro Systems

Macro Systems is a software business dedicated to developing applications in Excel and other Microsoft products.  If you would like to have a specialized set of macros built let us know.  For example, we've built macro systems that automatically loads data that would take hours to do manually.  So, if you're doing a repetitive task each day, week, or month, we can automate it and save you time and eliminate errors associated with such manual work.

 And, we've built very special models.  For example, we've built models that give the production cost and earnings by item for entire factories.  So if you need specialized Excel models or systems developed we're interested in working with you.

We can't think of a better way to advertise our services than with our products, and we consider The Spreadsheet Assistant™ a fine example.  If you're interested in contacting us, we can be reached by mail at:

Macro Systems

1008 Lawndale Road

Wilmington, DE 19810

or via CompuServe (ID:  72774,416).

or Fax 302-475-0142

or phone 302-475-8171 7-10PM EST.


Fill Highlighted Cells to the Right

This command copies the highlighted cells and then pastes them from the active cell to the right until the first blank is reached if there is text to the right of the active cell.  If there is no text to the right of the active cell, but there is text above the cell, then the first cell above with text is  used to determine how far right to copy and paste the selected cells.  The active cell must have an entry in it; it can not be blank.


Fill Highlighted Cells All the Way Right

This command copies the highlighted cells and then pastes them all the way to the right to the last cell on the active row that has an entry.  If there are no cells to the right, then the first row above with text is used as the basis to determine how far right to do the copy and paste.

One of the problems with this command is that it fills cells that you want left blank.  To remove these unwanted blanks, use the command Blank Rows based on Reference Row to automatically erase entries based on a reference row that you specify.


Fill To The Target Row

Quite frequently, the range you want to paste to is not nicely defined by having a blank cell right where you want to end your copy and paste.  For example, you may want to copy formulas down 10 rows but not affect the 11th row which has formulas that sum the first 10 rows.  That's where this command fits in.

First, you define a reference row, which is the last row to which you want to copy and paste.  You do this be running the command Set the Target Row/Column.  This command requests you to highlight a cell in the last column or row to be pasted to.  If all you are doing is filling to a target row, then the target column is unimportant.

Next you run the Fill to The Target Row command.  It copies the cells you have selected to and including the reference row.  Its that easy!


Fill To The Target Column

Quite frequently, the range you want to paste to is not nicely defined by having a blank cell right where you want to end your copy and paste.  For example, you may want to copy formulas to the first 12 columns but not affect the 13th column which has formulas that sum the first 12 columns.  That's where this command fits in.

First, you define a reference column, which is the last column to which you want to copy and paste.  You do this be running the command Set the Target Column/Row.   This command requests you to highlight a cell in the last column or row to be pasted to.  If all you are doing is filling to a target column, then the target row is unimportant.

Next you run this command.  It copies the cells you have selected over to and including the reference column, and no further.  Its that easy!


Hide and Save The Macro File
Save The Spreadsheet Assistant Settings

If you customize part of The Spreadsheet Assistant (such as the dialog boxes or option button choices), you will probably want to save the setting so that they are set the way you want in your next Excel session.  This command, which is found near the bottom of the command list, saves ASSISTNT.XLS, which is where your settings are stored.

For example, you may want to save the settings if you prefer a different configuration on the command "Initialize selected sheets", or if you prefer to different default on the insert blank rows (ex: every 3 rows vs every 5) command.


Fill Highlighted Cells Down

This command copies the highlighted cells and then pastes them from the active cell down until the first blank is reached if there is text below of the active cell.  If there is no text below the cell, but there is text to the left of the cell, then the first cell to the left with text is  used to determine how far down to copy and paste the selected cells.  The active cell must have an entry; it can not be blank.


Fill Highlighed Cells ALL the Way Down
Fill Highlighted Cells ALL the Way Down

This command copies the highlighted cells and then pastes them from the active cell all the way to the last cell in the active column that has an entry.  If there are no cells below the active cell, then the first column to the left with text is used as the basis to determine how far down to do the copy and paste.

One of the problems with this command is that it fills cells that you want left blanked.  To remove these unwanted blanks, use the command Blank Rows based on Reference Column to automatically erase entries based on a reference column that you specify.


Insert a Big Dot

This command is great for improving a presentation!.  The following illustrates the big dot and its effect on a presentation:

So no more remembering what font and key strokes are needed to put a dot into your spreadsheet.  Use the dots and thick dashes to make presentations written in Excel stand out.


Load Data From One File to Another

The best way to illustrate what this command does is by way of an example.  Say that you routinely obtain a file containing a list of sales dollars by customer and need to store in your spreadsheet also by customer.  Most users would go to the spreadsheet and search and type, search and type,....  No more!  Assuming that you can get the data into Excel (by loading a text file or just typing it in), this command will do that searching and loading for you. (You may need to trim blanks after importing text.  Use the Trim Blanks command to do that for you)

This command asks you to do the following before it starts work:

1) Identify the input data range,

2) Specify a column which contains data identifiers for the data (such as customer names),

3) Specify the column in the destination spreadsheet where the data is to be loaded, and

4) Specify the column in the destination sheet that identifies the data in that sheet.

Once all this is done, you have one last option.  That is to indicate a "flag column" that can be used to identify data that is not loaded.  This option is optional.  In all cases data that is not loaded is colored yellow, and data without an identifier is colored red.  The advantage of the flag column is that it allows you to sort your data to group all the not loaded data together.

Several warning about data identifiers.  They should be unique.  If not, then the data will be loaded into the first matching row.  Also, a difference in spaces, anywhere in an enty or at the ends makes it different from an entry with the same text.


Trim Blanks

If you import data into Excel from ASCII files or copy data from one program via screen copies into Excel, you will quite often end up with spaces in front of or following the text that has been copied.  The trim blank command eliminates the pain of having to manually remove those spaces.  Just highlight the range for it to act on and let it loose.

Please note that if there are extra spaces between words, these extra spaces are also removed.


Activity Timers

If you need to keep track of how long you work on a given project, then there are 5 activity timers available for you.  They can all run at once, allowing you to time different parts of an overall task.  And, they can be suspended or reset as needed.  The following screen illustrates the timer panel.

You can get to this timer panel very quickly by selecting it from the QuickAccess Menu!

To activate a timer, just click on the check box so that an X appears in it.  To suspend timing, just click to remove the X.


Print the Macro List
Print the Command List

This command does just that.  This way you can make notes on the list about the various command, decide what you want to run, and show the list to all your friends so that they will also buy the Spreadsheet Assistant!


Remove or Show Macro Commands on the Excel Menus
Remove or Show The New Menu Commands

One thing that the Spreadsheet Assistant does is that it puts a number of commands under Excel's menus.  Like a new menu item to save all files is now found under the File menu.  And there are more.  But if you don't want them added, you can remove them (except for one or two that give you access to The Spreadsheet Assistant menu and its help) from the menu's using this command.  Or you can add them back with this command if you previously had removed them.  This command also adds and removes the Quik menu.


Color Cells Based on IF Test

This command allows you to change the color and appearance of the cell and its contents based on an IF test.  The test may be a numeric test, or it may be a text test.  The following panel shows the various options available:

If the Colorize box is checked, then there are further options:


Set the Target Column/Row

This commands needs to be run before you run any of the following four commands for the first time.

Fill To the Target Column

Fill to the Target Row

Select to Target Column

Select to Target Row

 	This command allows you to specify the target row and/or column.  The target row has meaning only when you use the fill or select to the target row commands.  The target column only has meaning if you use the fill or select to the target column commands.

The direction of the select or fill that occurs is to the target row or column whether it is above or below the active cell (for the row commands), or to the left or right (for the column commands).


Select to the Target Column

This command selects from the active cell to the target column.  The number of rows selected is based on the highlighted rows.  If the target column is to the left, then cells to the left are selected to and including the target column.  If the target column is to the right, then cells to the right are selected to and including the target column.


Select to the Target Row

This command selects from the active cell to the target row.  The number of columns selected is based on the highlighted columns.  If the target row is to below the active cell, then cells down to the target row are selected to and including the target row.  If the target row is above the active cell, then cells up to the target row are selected to and including the target row.


Sticky Notes

Sticky notes are an easy way to put a note in a spreadsheet that does not go into a cell and is at the same time clearly visible.  Furthermore, you have the option to make the note printable or non-printable.  The latter is very useful for a quick documentation note or reminder.

When you select the stick note option, a box pops up giving you a number of options.  The following illustrates this box:

To delete a sticky note, just click on it and press the delete key.

This command is found not only on the command list, but also under the Edit menu.


Select Cells to the Right
Select Cells to the Right of the Selected Range

 	The select to the right command selects all cells from the highlighted cells to the first blank to the right.  If there are no cells to the right of the top cell in the selected range, then the first row with data above the selected range is used as the basis of selecting cells.


Select All Cells to the Right

The select to the right command selects ALL cells from the highlighted cells to the last entry to the right.  If there are no cells to the right of the top cell in the selected range, then the first row with data above the selected range is used as the basis of selecting cells.


Select Cells Below the Selected Range

This command selects all cells below the highlighted cells to the first blank.  The first blank is based on the first column in the selected range.  If there is no data below the first cell in the selected range, then the first entry to the left is used as the basis of the selection.


Select All Cells Below the Selected Range

This command selects ALL cells below the highlighted cells to the last entry in the first column.  If the first column is blank,  then the first column to the left is used as the basis of the selection.


Months Down or Across

How often do you have to type in the names of the months?  Microsoft Excel has a nice feature, drag and fill, to do this - if you have a slow computer.  But if you have a fast one, how often have you zoomed to far left or down and then over-corrected, etc.?  Well, we got tired of this, thus this command.

This command is both on the command list and under the Insert menu.  The following selection box appears when this macro is run:

Pick the option you want and presto, the entries are made!  No more dragging and guessing or overdragging!


Insert Date, Time, File Name and Sheet Name

We also got tired of typing in the date and time on worksheets and having to format the cells and range value the result (when we used the NOW() command).  Thus this command was born.

This command is both on the list and under the Insert menu.  The following selection box appears when this command is run:

This command allows you to enter the date and time, just the date or just the time into a worksheet.  And, you don't have to browse a massive list to find your favorite format.  It stores your format for you so that you don't have to search a format list for the a desired format.  If you load in your desired formats, be sure to save the Spreadsheet Assistant setting using the save settings command on the command list.  Otherwise, they will not be there the next time you load the Spreadsheet Assistant.

To create a custom format, use m's for the month, d's for the days, y's for the years, h's for hours and m's for minutes, and s's for seconds.  You may also use the characters "/", ":", "-" and spaces to further define the format.  The following table shows you what the various combination of entries do:

Entry	Samples

DAYS:

d	1, 2, 10

dd	01, 02, 10

ddd	Mon, Tues,...

dddd	Monday, Tuesday,...

MONTHS or MINUTES

m	1, 2, 10

mm	01, 02,10

mmm	Jan, Feb,...

mmmm	January, February, ...

YEARS

y	93, 94,95,...

yy	93, 94

yyy	1993, 1994,...

SECONDS

s	1, 2, ...., 10, 11,..

ss	01, 02,...., 10, 11, ...

HOURS

h	1, 2, ... ,10,11,...

hh	01, 02, ...10, 11,...

The following are some sample formats and their result:

Sample	Result

m/d/y	1/1/94

m/d/yy	1/1/94

mmm d, yyyy	Jan 1, 1994

mmmm d, yyyy	January 1, 1994

h:m:s	1:22:33

h:m am/fm	1:22 AM


Insert a Circle

This command inserts a circle around the selected cells.  This command is located not only on the command list, but also under the Insert menu.  This is a quick way to draw a circle without having to display the draw toolbar.  And, once the circle appears, you can resize it as needed.  To delete a circle, just click on it and press the delete key.


Blank Cells Based On Reference Row

If you used the command

Fill Highlighted Cells ALL the Way to the Right,

then you will probably end up with cells that shouldn't have entries in it.  For example if you have skip columns to improve readability, then those skip columns will have had cells copied into them.  That is where this command helps.

First,  select the range of cells (multiple rows allowed) that need cells blanked or erased.  Then run this command (Blank Cells Based on Reference Row).  You are first asked to specify the reference row.  Once this is done, the command takes over.  It compares the selected cells to the reference row.  If the cell entry in the reference row is blank, then the cell in the selected range is erased.


The Commands Added to the Excel Menus

The Spreadsheet Assistant not only gives you a list box that allows you to select a command, it also adds quite a few of the most likely used macros to the Excel Menus.  If you want to remove the commands, you can selectively do so by using the command near the bottom of the command list that adds or removes commands from the menus.


Delete the Active File

This command displays a menu which gives you three options:

- Delete the active file

- Close but do not delete the active file

- Do Nothing!

The names of the active file and its directory are displayed above the three options.  If you choose delete, the file is first closed and then deleted from the disk.

One way to use this command is to first open a group of files by holding the control key down and highlighting the files to be opened.  Then, after the files are opened to repeated run this command using the repeat key.


Insert Blank Rows If Cells Are Different

Lets say you have a set of data and wish to insert blank rows every time the contents of the selected column changes.  For example, the rows may contain data like the following

Representative	Customer

John	IBM

John	Microsoft

Bill	Dupont

Bill	Ford

Bill	GM

Linda	US Air

If you highlight the Representative data and select this command, it will automatically insert the number of rows you specify whenever the representative changes.  The result is a much more readable set of data.  And, if you need to remove the blanks rows, there is a command called "Remove Blank Rows" that will remove the blank rows for you.

The first column in the selected range is the one that is used to determine if row entries are different.  Also, you have the option of inserting blank rows or shifting the selected text down.


The QuickAccess Menu

The Spreadsheet Assistant adds a new menu called QuickAccess  (Actually "Quik" on the Excel menu).  It provides quick access to the following Windows features:

Calendar

Card File

Character Map

Date and Time

Control Panel

File Manager  or Windows 95 Explorer

Notepad

Print Manager (Windows 3.1 only)

Recorder

Write

Some installations of Windows 95 do not have a number of the programs required to run the above commands (typically the recorder, Character Map, and the calendar).  However, you can find the programs on a Windows 3.1 machine.  And, if they are copied to your Windows directory, the above commands will execute them.

In addition to easy access to the above Windows features, it also has selections that give you quick access to:

Program Manager (if running Win 3.1) or the Windows 95 Desktop

DOS

Lastly, the QuickAccess menu also provides access to the alarm commands and the timer commands that are supplied with The Spreadsheet Assistant.


Save All Files

The Save All Files command helps you to save all open non-hidden files without having to go to each file individually and do the commands.  As each file is saved, the Excel display at the bottom of the screen is activated to show you the save activity.


Close All Files

The command Close All Files is a quick way to close all non-hidden files.  If the file has been modified you will be prompted to save the file before it is closed.  Please note that Excel is extremely sensitive to what it perceives as changes to a file.  Sometimes if you've saved all the files and then close them using the close command, Excel will prompt you to save the file again.  Your decision.  However, you know what you've done and if the file really needs to be saved again.


Colorization Commands

The purpose of these commands is pretty obvious from their titles.  Hopefully, they will save you some time.  The Blue and bold format command is a useful way to highlight cells that contain data entries vs those containing formulas.

Color Selected Range Light Gray

Color Selected Range Dark Gray

Blue and Bold Text

Bold Cells Based on Reference Column

The last command above allows you to highlight a range (or column) and have the command check the bolding in a reference column.  If a cell in this row is bolded, then the cell on the same row in the selected range is bolded.


Copy Column Widths
Copy Column Widths And Row Heights

Thise two commands are useful when you are copying information from one file to another and realize that you've also need to set the column widths or row heights the same.  The columns or rows in the two sheets do not have to be the same.  For example, you can copy the widths of columns K-Z into another file or sheet starting at column C.  In fact, the rows and columns can be in different places in the same sheet!


Questions and Answers

Q - I don't have an XLSTART directory under my EXCEL directory.  Where should I I install the ASSISTNT.XLS file?

You can either create a directory named XLSTART under your EXCEL folder or create a dirctory with any name you want and under TOOLS, OPTIONS, GENERAL specify this directory.

Q - I get the message "Can't find help file ASSISTNT.HLP..." when I ask for help on one of the commands or on the Spreadsheet Assistant.

To fix, you need to put the file ASSISTNT.HLP in your Windows directory.  If you are running Windows 95, this directory may be named WIN95, Windows 95 or something similar.

Q - Excel opens the file ASSISTNT.HLP when I start up Excel.  It gives me the message "Can't open this binary file... Open as text?".  If I say yes, it looks awful strange.

The file ASSISTNT.HLP does not belong in your XLSTART directory.  Delete it, and install the one from your installation disk into your Windows directory.

Q - I get the message "File Format Not Supported" when Excel tries to open ASSISTNT.XLS.  How do I fix?

The file STDOLE.TLB, which Microsoft Excel uses, has been replaced when new software was loaded on your computer.  And this version is not compatible with Excel Visual Basic.

The recommended cure from Microsoft is to rename this file (to STDOLE.OLD) and then run the reinstall Microsoft Excel option from your disks.  This file is found in the WINDOWS\SYSTEM directory.  The reinstall option looks for missing components and only installs them.  Thus you are not doing a complete install.

You can also try replacing this file with a copy from a computer where Microsoft Excel can read workbooks containing modules.  Included on the disk containing the Spreadsheet Assistant files is a copy of STDOLE.TLB that works with Visual Basic.  Please note that there is no guarantee that this second approach will work.  Also, you will need to restart Windows after replacing this file to see if it works.  If this second approach does not work, please use the approach that Microsoft recommends.  If you continue to get the error message, then you need to contact Microsoft at 206-635-7070 as this is an Excel problem with your copy of Excel.

Q - I would like to set some of the defaults in the commands differently.  Is there an easy way to do this?

This is very easy to do.  First, run; each of the commands you want to customize and set them the way you want them.  Then select the command near the bottom of the command list titled "Save The Spreadsheet Assistant Settings".  Be sure that you have set worksheet calculation the way you want it set first (Excel sets calculation other other options based on the setting in the first file that is opened.)

Q - I created a customized button and now it no longer works (it worked initially).  I get the error message "A document by the name of ASSISTNT.XLS is already open.

What's happened is that you have moved ASSISTNT.XLS to another directory after you created the button.  Delete the button (or toolbar if it is its own toolbar) and recreate.  To delete a toolbar, select View, Toolbars, highlight the toolbar label and select delete.  To delete a button that you've created and moved to another toolbar, select View, Toolbars, Customize.  Then drag the offending button off the toolbar and release it in the customized box.

Q - I get the error message "Macro Z_Select_Macro_To_Run" not found or the error message "ASSISTNT.XLS not found".

What has happened is that you moved ASSISTNT.XLS from one directory to another using a file manager and it is not in a startup directory.  If you first manually open ASSISTNT.XLS, it will delete the old toolbar and create a new one, and things should work fine.  If you have created buttons, you will need to delete them and recreate them.  And, in the future as long as you don't relocate the file, clicking on the button will also automatically load the file.  Putting the file in your startup directory is preferred as this allows the Spreadsheet Assistant to add commands to the various Excel menus upon startup of Excel.

Q - How do I control the calculation option?  No matter what I set it too, it changes the next time I load Excel.

When you save a file, Excel stores the calculation option with the file.  For example, if you turn calc off and save a file, the calc off setting is stored with the file.  The purpose of this is simple:  When you load the first file into Excel, Excel sets the calculation option based on what was stored in the file.  Normally this doesn't create a problem.  To solve, make sure the calculation setting is the way you want it when you save The Spreadsheet Assistant's settings.   Why?  Because ASSISTNT.XLS is often the first file you open since it is in the startup directory, and thus sets the calculation option to the way it was when the macro file was saved.

If this doesn't solve the problem, unhide any hidden files that you have (for example PERSONAL.XLS) and save them with the calculation option set the way you want it.

Q - Do I have to put the file in my startup directory?

No, but if you do, this allows the additions to the menus to be immediately available.


Set Print Titles

This command is very useful if you first assign it to a button on one of your toolbars.  The name of the macro to assign to the button is Z_Set.Print.Titles.  To add a button to one of your toolbars, right click on the toolbars and select customize.  Then select the button to use from the custom buttons.  Specify the macro and close the menus.

To use the command, first select the rows and columns to be the print titles.  To select both at the same time, first select the rows and then hold the CTRL key down and select the columns.  Once you do this, run the command (or if you've assigned it to a button, click on the button).

This command is also found under the Insert menu.


Converting Trailing Negatives to Leading Negatives

This command converts trailing negatives such as "123-" to leading negatives (ex: -123).  Many files imported from mainframes use trailing negatives, which Excel interprets as a text entry versus a negative number.  This command eliminates retyping these numbers.  To use, highlight the range you want to convert and click on the command.  Multiple ranges can be selected at one time.


Repeat Find Command

Microsoft has a repeat button, but it repeats the last command, which is not necessarily the find command.  You can also use shift-F4 to repeat a find.  However, you have to be a contortionist or use both hands to use shift-F4.  So, We've added a repeat find command.

If you need to use this command frequently, then you should assign it to a button.  If you don't need it this frequently, then you can use The Spreadsheet Assistant repeat button to repeat your searches (as long as it was the last command run from the command menu).


Flip Calculation Between Automatic and Manual

To change from automatic calculation to manual calculation (or vica versa) requires several keystrokes and reading of dialog boxes before one can change the calculation setting.  This command allows you to "flip" between automatic calc and manual calc with a minimum of keystrokes.  And, it only changes this setting, other settings are not affected.  It also displays the new setting for a second at the bottom of the screen.

This command is available not only from the command list, but also under the Tools menu.

Please note that if you want calculation set to automatic except for tables, you will need to make this setting manually.


Round to 10s, 100s, etc or UnRound

How often do you have a set of numbers that you want to round to the left of the decimal?  For example, you want to round 12736 to 12700 or 12000?  This command wraps the code in the selected cells with the Excel formulas that will round the cell values to the requested number of zero's.  And, if later you decide that you don't want the formulas to be rounded, it will automatically remove the formulas.  Multiple non-contiguous areas can be selected at one time.

For the un-round option to work, you can not have modified or added to the formulas in the cells.


Favorite Directories

The favorite directories command allows you to store your top 27 favorite directories and quickly select a directory and go to it and open files.  To add directories, select the command, and then the ADD option.  In the edit box that appears, list the full path and directory name.  For example:  C:\EXCEL or C:\ACTIVE\PERSONAL.  Please note that the names do not end with a \.

Once you have filled in the names or after making a change to the names, click on the SAVE button.  This makes the change permanent by saving the Spreadsheet Assistant settings.

To go to a directory that is listed in one of the boxes, select the directory and double click or select the SELECT button.

This is a great command to assign to its own button.  Just highlight it in the command list and select the assign to button option.  Then edit the button image.  Further, remove the standard Excel button and replace it with the new one on your menu bar.


Formating Numbers based on an IF test
Formatting Numbers based on an IF test

This command allows you to format numbers based on their value.  For example, you may want to format all numbers between 0 an 2 to one decimal and all numbers larger than 2 to no decimals.  To do that using this command, first format the range to no decimals.  Then run this command and specify 0 and 2 as your test values.  And specify the format.  The command checks each cell and then does the formatting.  Please note that if cell values change, the macro will need to be re-run.


Compare Cells, Colorize Duplicate Entries

This command is very useful if you want to insure that the contents of a column are unique.  To use, first sort the data in the column (you may want to copy to a separate temporary sheet and then sort this sheet.)  Then highlight the range to be be checked.  When a duplicate entry is found, the command stops and asks if you want to continue.  The duplicate entry is colored green to highlight it.  Please note that blanks are skipped over, and that the selected range can be only one column wide.


UnHide the Column To The Right

This is a very useful command if you frequently have to unhide columns.  Just place the cell pointer to the left of the column to be unhidden and run the command.  It will unhide the column to the right.  If you need to unhide several columns, then just run this command repeatedly using the repeat button, which gets programmed to run this command once its been run once from the menu list.  This command is also found under the Format menu.


Hide and Protect A File
Hiding and Protect The Active File

This is a useful command if you want to make it difficult for others from getting access to a file containing a set of information or macros. (I can't say prevent because there are a few people in the world who can do anything!)  It also prevents users from browsing the file with a browser like the one that comes with Windows 95.  (Some other browsers may be able to browser the file).  This command does the following:

It hides your file

It will hide any Visual Basic Modules so that they can not be manually unhidden

It will then protect the file after you provide a password.

It will then save the file.

The primary benefit of this set of actions is to prevent access in Excel to the file by others, and to prevent them from stepping through the macro code to see what the macros are doing.  And since it is protected with a password, the user can't unhide it or view it.

If you want to get access to the file, you will need to manually unhide the file and to unprotect the file.  Then run the command Unhide All Sheets that unhides the modules if you chose the option to hide the modules in the previous command.


Selecting Special Cells
Select Cells with Numbers Only, Formulas,  Or #REF!

In a typical worksheet, you will have cells that contains numbers and cells that contain formulas.  However, it is often difficult to remember which cells are which.  These commands will do the following:

Select the numeric cells which contain only numbers

Select the numeric cells which contains only formulas

Select any cells containing #REF!

For example the first command will select cells with just a single number entry, cells with an equal sign and a number, and cells with several numbers.  An example of the last case is "=5+10+15".  Once all these cells are selected, you can change the format on the cells, delete the contents, or tab through the cells by pressing the enter key.

The select formula cells command picks all numeric cells that are not numbers.  And the select #REF! command  helps you find cells containing #REF! without having to do a lot of manual searching.  By pressing enter after running this command, you will be taken to each selected #REF! cell.


Hide The Entire Row If Entry In Cell

Some users have several reports that have both detailed entries and summary rows.  Sometimes they only want to see the summary rows and sometimes they want to see all the rows.  To solve this problem, the Spreadsheet Assistant has two special commands.  One hides the row if there is an entry in a selected cell.  The other unhides the row if there is an entry.  This second command leaves hidden any rows that have been manually hidden and do not have an entry in the range used to specify hide or unhide.

To use, simply pick a column and put an entry in the cells of the rows you wish to hide.  For example an X, or the word "hide".  Then highlight a range of cells in this column or the entire column and run the command.  To unhide, just select a range in this column or the entire column and run the command that unhides  the row if there is an entry!.


Add or Remove Page Breaks Based On Cell Entry

Every now and then you may need to print a report in landscape mode that is normally printed in portrait mode.  The problem you may have run into is that the page breaks are in the wrong location.  To fix, Spreadsheet Assistant has a command that checks the entry in a range that you've selected, and if it finds an entry it adds a horizontal page break.  If it does not find an entry and there is a page break, the page break is removed.  The entry can be anything, for example an "X" or the word "Break".  Normally, you would set aside two columns, one for the portrait breaks and one for landscape breaks.


Hide And Save The Active File

If you create files that you want to hide when not in use or when you distribute them, this command will first hide the file and then save it.  That way, the file is hidden when you open it.  This command is also useful to run on your Personal.Xls file as a way to keep it hidden when not being edited.


The Loan Calculator

If you ever need to determine the cost of a loan, the loan calculator does the job for you.  Just enter the amount you want to borrow, the interest rate, and the number of payments, and the command calculates the payment.  It can handly any number of payment periods - weeks, years an months.

On the interest rate, you enter in both the rate and the number of periods the rate applies to.  For example, if the rate is 7.5% per year and there are 12 payments in a year, you would enter 7.5 and 12.  If the amount is $1000, then the monthly payment would be $87.  Please note that some banks may figure the amounts differently, and they have the last say.


Pivot Table Commands

There are several commands in the Spreadsheet Assistant that help on pivot tables:

A recalc all pivot tables command

A highlight total rows command

A add a pagebreak and underline command

The first command checks each worksheet for pivot tables and then runs the commands that refreshes any tables that it finds.  T

To use the second command, you must first place the cell pointer in a pivot table.  It will then search the first column of the pivot table for the word "Total" at the end of the entries.  When it finds a total row, it will widen the row width, vertically center the entries on the row, bold the entries, and draw thick border lines on the top and bottom edges of the total row.

The third command solves a problem when you print long pivot tables.  Excel does not underline the last printed row.  The result is poor appearing printout.  Before you use this command, place the cell pointer on the row where you want a pagebreak inserted.  When you run the command, it will add a pagebreak and underline the pivot table cells in the row above the pagebreak.  The result is a much nice printout.  Please note that you will not see the underlining because of the pagebreak.  But it is there!


Tool Bar And Tool Tip Editor

If you create new toolbars, Excel names them "ToolBar1", "ToolBar2", and so on.  If you manually assign a macro to a custom button, the tool tip on the button is "Custom".  Not to meaningful.  This command allows you to rename toolbars you have created to any name you want.  And, you can delete toolbars very easily.

You can also repeatedly show and hide a toolbar so you know which toolbar is which while you are working in the toolbar editor.  You may have to move the editor box in order to see a toolbar if it is a floating bar, and its location is "under" the editor box.  If you move the editor box and the previous location stays grayed out, exit and the editor and re-run.  This normally cures this problem.

 	he tooltip editor allows you to change a tooltip, and if you want, to delete a button.  And, when you click on a button listing, the name of the macro assigned to the button is shown at the bottom of the dialog panel.


What's New

We've tried to make the commands as bug free as possible.  If you come across any, please let us know.  We have noticed that some of the commands get upset if run on a protected worksheet, and rightly so.  (However, we don't consider this a bug!)

The following is a history of the updates and changes to The Spreadsheet Assistant™.  Please note that many of the changes are upgrades and additions vs bug fixes and reflect our commitment to continually improve the commands.

10/2/96

Fixed a number of the print macros so that they work on protected worksheets.

10/2/96

Changed the blank column based on reference column command to copy any text as opposed to just underlines from the refernce column.  This function is optional, but if used, allows copying of column headers in addition to underlines.

"Cleaned" the VB code, reducing file size 10%

9/29/96

Added a set of commands that shift the cell format to the left a space at a time, or removes the spacing.

Added two commands that can be assigned to buttons:  one that pastes just formulas, and one that pastes everything but borders.  The second is an Excel 7 or higher command.

9/28/96

Added a second set of commands to store locations and return so that you can assign to buttons to toggle between locations.

9/27/96

Added a commandthat exports a report to a XLS file.  A report is the print area and titles of a sheet.  This command should be very useful to any group who wants to post XLS files on an Intranet or attach to E-mail notes.

9/19/96

Re-arranged the main menu to better organize the commands.

Modified the any math action command so that it remembers the last setting used by the user if the command file is saved.

9/18/96

Added Row and Column counts to the range information command

Added the option to toggle use of commas off and on in the format commands that appear under the Format menu.

Added a command that both adds a pagebreak and underlines the row above the pagebreak in a pivot table.  This greatly improves the appearance of printed pivot tables.

9/14/96

Added commands to select unprotected cells, protected cells, and to easily protect and unprotect cells

Added a command to toggle view formulas off and on.  This command is also available under the View menu.

Added a Range Information command that returns the sum, average, min, and max information on the highlight cells, and allows you to paste the selected value in a cell.  It also gives you cell count information

Added a command that allows you to rename the active file

Added a command that returns the full path of the active file and allows you to set this as the default directory

9/11/96

Updated the Quik menu to advise users to copy Windows 3.1 programs to their Window directory if they have converted to Windows 95.

9/8/96

Checked the commands that change data and found that some did noto turn calculation off while the command is running.  Modified them to do so and to turn calculation back to the user setting when done.  This will significantly increase the speed of these commands.

Added a tool bar and tool tip editor.

9/7/96

Changed the Save All Files and Close All Files commands to Visual Basic code, which is more efficient than the Excel 4 language on these commands.

Added a command that calculates loan information.

Added two pivot table commands - one to refresh all pivot tables in a workbook, and one to highlight total rows.

9/6/96

Added in a new command that copies row heights from one location to another, even across workbooks.

8/30/96

Modified the perform math action command to reset calculation to the user's setting if no numeric cells are found.

Added a command that hides and then saves the active file.  This makes the file a hidden file when it is next opened.

8/25/96

Replace the Inputbox on the math action command with a dialog box.  This avoids accidently clicking on a cell and putting in a cell reference.

8/17/96

Added a command to select all formula cells

Added a command to select all cells with #REF!

Added a command that bolds a cell if a cell in the same row in a reference column is bolded.

Added a command that hides rows if there is an entry in the range selected.  Useful if you have a report that has both summary lines and detail lines.  Allows you to hide the detail lines.

Added a command that unhides rows if there is an entry in the range selected.  Its the undo of the above command!

Added a command that adds a page break if a cell has an entry and removes any page break if there is not an entry.  Allows one to control page breaks when shifting between landscape and portrait mode.

8/9/96

Replaced the commands that allow you to multiply and divide by 10, 100, etc with a set of Visual Basic commands.  The commands that were replaced would sometimes not work due to a bug in Excel.  By going to Visual Basic, this problem should be eliminated.

Added a command that will select all number cells in a selection.

Added commands that hide your workbooks and modules that contain command code so that users can not get at the code.

Changed the name of the command file to "T_Macros.XLS" which is short for The Macros.  Also changed the name of the program to "The Macros".

7/9/96

Fixed the help file for Windows 95.  Previously, if you went directly to a help topic and then clicked on the contents tab, Excel's help menu would show up instead of the command content menu.

7/6/96

Fixed Indent text command when text is truncated and no text remains.

12/16/95

Added a message when running the load data from one file to another command that shows the count of the data being processed.

12/12/95

Added a Go To Explorer option to the File menu if Windows 95 is running.

12/3/95

Added the Delete a File command to the File menu, and took off the initial selected sheets;  The initialize command is still available from the command list.  Also, a template spreadsheet can be set up to provide the same result.  Also, added the command to display the bottom of the print area to the view menu.  And added the commands to set the print area and title to the insert menu.

10/17/95

Add a message when the command that compares for duplicates is run that lets you know if there are no duplicates.

10/1/95

Add a command that compares entries in a column to identify duplicate entries.

9/17/95

Added Windows 95 options to the QuickAccess menu

9/7/95

Checked all commands.  Minor changes made to several ones.

9/2/95

Modified the Insert Months command to give case control option.  Also, modified the favorite directories command to make the display box bigger and the purpose of some of the buttons more obvious.

8/8/95

Decided to enhance the format number based on IF test command.  The enhancement includes font and background formatting as well as bold and outline formatting.  Also, the ability to specify OR or AND test was added.

8/5/95

Added a command to format numbers based on an if test.  For example, you can format a range to 0 decimals,and with this command go back and format the small numbers to one or two decimals.

Modified the store print area specifications and apply specifications commands to work in a more logical fashion!  Also, removed the option to remove automatic page breaks as this was a screen display option only.

7/24/95

Add a new command called "Favorite Directories" which allows you to store a list of your top 27 directories in any order.  This allows you to easily change directories and open files.  This command should be assigned its own button and the default open files button replaced.

7/12/95

Fixed an incorrect range name that prevented the divide by any value from being used from the command menu in the Excel 5 version.  It worked OK from the Tools menu.

6/10/95

Added the Quik menu to the menus that appear when one has a chart sheet displayed.

Added the commands "goto file manager", "save all files", and "close all files" to the Chart File menu.

5/21/95

Added in the Excel 5 release an option under the View menu to toggle grid lines off and on.  This option is easy to get to in Excel 4, but difficult in Excel 5.

Added a new command, which adds formulas to cells to round the values to the left of the decimal.  For example 12736 becomes 12730, 12700, 13000, etc depending on how many zeros you want to see.  And, the same command can remove the code it adds.

Improved the command that adds rows or columns to the print area.  Before, the command only added rows or columns to the bottom and left side of the print area.  Now, you can add at one time rows to the top and bottom, and columns to the left and right.

Added an error check to the multiply and divide by commands to confirm that the equations were written back correctly.  There is a bug in Excel that happens about one out of 500 Excel sessions that causes an error with formulas created by this command.

5/14/95

Added a command that unhides the column to the right of the cell pointer.  This command is available on the Macro list, and in Excel 5 also on the format list.

Add a selection under the Options menu in Excel 4 and the Tools Menu in Excel 5 to repeat the last Bob's Macro command.  This way, users who do not want to display Bob's Toolbar do not loose the repeat command option.

5/8/95

Fixed a problem in the Excel 5 release of the commands.  If the paste to column or row has not been set, the paste to the target row or column command continued to run instead of stopping when this situation was detected.

4/30/95

Add a cancel option to the Save All Files command and to the Close All files Macro.

Put a "»" symbol in front of all the new commands added to the Excel menus.  This way, a user will know if the command on the menu is one which comes with Excel or one added by Bob's Macros.

4/24/95

Modified the create an ASCII command so that any thick underlines or dashes created by Bob's Macros are converted to normal dashes (-).

4/22/95

Add a command that takes a user directly to the command code.  Please note that only registered users get access to the code.

4/20/95

Added a command to the command list and to the tools menu in Excel 5 that flips calculation between automatic and manual settings.  Excel 4 didn't need because access to the calculation options is very convenient.

3/25/95

Added a command that converts trailing negatives such as 123- to leading negatives (ex: -123).  Many files imported from mainframes use trailing negatives, which Excel interprets as a text entry vs a negative number.  This command eliminates retyping these numbers.

3/7/95

Fixed a problem with the Indent Text command.

3/4/95

Added a repeat find command to the Excel 5 release.  This allows this feature to be assigned to a button for ease of use.

Removed the "All Done" messages that appeared in several commands.  This was done to allow the commands to be called by a user's command and not required the user to acknowledge the message in order for the user commands to continue.

2/24/95

Modify the commands so that all the additions to the Excel menus are added if the file is opened by clicking on the Bob's Macros icon.  This allows the file to be stored other than in XLSTART directory.  However, additions to the menus only occur after clicking on the icon if the file is not stored in the XLSTART directory.

Added a spacer to the menu that pops up when you left click on a cell.  This makes it easier to pick the fill vs select commands.

Add the command "Add or Subtract Any Value" to the data menu in Excel 5.

2/6/95

Modified the commands that prompt for an input area to suggest the currently  selected range as the desired area.  The user still has the option to change the range.  Macros changed where the Create an ASCII file command, Compare Two Columns command, and the Load Data command,

Modified the Load Data command to allow multiple columns to be loaded at one time.

2/5/95

Modified the command that sets selected sheets to the same upper left cell and zoom so that if no sheets are selected, all sheets are automatically selected.  Also, added this command to the Window pull down menu (replacing a little used command that was previously on the pull down menu).  This change affected only the version 5 model.

1/7/95

Fixed a problem with the create an ASCII file command that occurred when header columns are specified, but header rows are not.

1/4/95

Added a new command called Print Current Selection and then Restore the Print Area.

12/26/94

Modified the load data from one file to another command to make the colorization of data that is not loaded optional.

Modified the compare two columns command so that the screen does not end up showing column IV when the compare is done on the contents of column A when they are title columns.

12/10/95

Added 800 number for orders, and provided a discount for orders for both the commands and ReportRunner.

11/29/95

Add a command to set print titles to the Excel 5 release.  By assigning to a button, it makes setting print titles far easier.

11/15/95

Change the pattern of the dotted lines in the box and outline commands to give a finer dotted pattern.

Added an explanation in the Question and Answer section as to why users get a command not found message and how to cure (Its caused by moving the file, and all you do is manually load the commands to cure).

Modified the commands so that they can be called by user written programs, and then allow the user commands to continue executing.  Prior to this, the commands halted at completion of their commands and did not return control to the user command.

11/14/94

Fix bugs in the "Load data from one file to another command" and in the "Relocate cell to upper left.

Modified the command that assigns any of the commands to a button/toolbar so that an almost unlimited number of buttons/toolbars can be created.

Add in the Excel 5 version an option to create a button/toolbar with a user defined pop-up label.  The button can then be assigned to a user written command.

Modified the find a command command command so that it sets the find command to the correct setup prior to doing a search for a command.

11/7/94

Renamed the QuickAccess menu to "Quik" to provide room on the Excel menu for users to add their own menu.

Fixed a problem in the Create an ASCII command.  The command was not properly copying column widths.

Add a Question and Answer section to the help

10/31/94

Minor efficiency improvements to several commands.

Rewrote major sections of the help files, and add additional help.

10/29/94

Modified the commands so that users can customize the Excel menus upon startup.

Combined the two transpose commands into one and removed it from the Excel Menus.  It is still available from the command list from Bob's Icon.

Made the QuickAccess menu optional

Added an automatic save if the user changes which sets of commands are shown on the Excel menus.

Modified the Transpose Macros so that the cell to appear in the upper left corner after the commands are done is requested.  This better positions the screen after the commands are done.

Modified the Add Blank Rows Every X command so that the user can specify how many blank rows are inserted.

Add a new command, Add Blank Rows if Cells Are Different.

10/20/94

Modified the startup instructions so that the appearance of Bob's toolbar is determined by how it was last set when you exited Excel vs always being displayed.

Added a new command that allows you to delete the active file.

Add an option to the date and time command to also enter the file name and sheet name into the worksheet.

10/9/94

Modified the command that blanks cells based on a reference row so that the user specifies the reference row vs the command selecting the closest row in the direction indicated.

Added a command to Blank Cells based on Reference Row .

Fixed a bug in the add commands to the menus so that the data commands in Excel 5 are removed if the user chooses to not display them.

Modified format 0.0, 0.00, and 0.000 commands so that commas are

entered if the value is greater than zero.

9/29/94

Added Control Panel to the Quick Access menu

Changed the descriptor on the Initialize Sheet command to say "Initialize Selected Sheets" in the Excel 5 version

Fixed a bug in the Find command in the Excel 5 version.

9/25/94

First release!


Rename The Active File

This is another one that Microsoft forgot!  This command allows you to rename the active file without having to close the file, go to the file manager/Windows Explorer, and then re-open the file.  And, by being able to rename it when it is open, the links to the file from other open files are change to the new file.  Please note that if you rename a file and other files have links to it and they are not open, you will need to manually change these links.  In this case, use the Edit, Links menu choices.

If you include a new path when you rename the file, the file is renamed and moved to this new path.  Be sure to include the full path, starting with the drive letter.


Return Path And Change Directory

If you open a file from the File Manager or Windows Explorer, the default drive and directory that appears when you do a File, Open or Save is not set to that of the opened file.  This means if you have created a new file and want to save to the first file's directory, you have to manually change the drive and directory in the save as dialog box before you save the file.  This is a pain!  This command will tell you where the active file is located and gives you the option to change the settings in the file open and file save dialog box to this directory and drive with the click of a button.  Then, when you want to open more files or save a new file, you don't have to manually set these boxes!


Analyze A Range
Display Range Information

Have you ever wanted to highlight a range of cells and see what the values sum to?  Or what the average, minimum or maximum is?  Or find our how many rows or columns you have selected?  This command gives you all that information, plus a count of the different types of cells in the range.  And, the option to paste either a formula or a value that is the sum, the average, the minimum or maximum in the range into any cell.


Cell Protection Macros
Cell Protection Commands

To make using cell protection easier, there are several commands that will help you:

A command that selects all unprotected cells

A command that selects all protected cells

And two commands that either protect or unprotected selected cells.

If you only select a single cell and run one of the select commands, then the command will check all cells in the active sheet.

If you do use cell protection, it is a good idea to color code the unprotected cells so that you can easily be recognized.  Also you will need to turn on the protect option on the sheet for cell protection to work.


Toggle View Formulas Off And On

This command, which has been added to the View menu, toggles the view formula option off and on.  This makes it very easy to check your formulas and spreadsheet design without having to select tools, options, find the right tab, and change the option setting.  And then repeat the process to undo formula view.


Export A Report To A XLS File

The typical way to share information in an Excel file with others is to  print the result.  This command gives you an alternative.  It creates a copy of the active sheet, but only keeps the print area and the report titles and rows.  All other information is deleted.  And, any formulas in what remains is converted to values.  You have the option of putting the report into a new workbook or in any workbook that is open.

When you are done, you have a file that is much smaller than the original files, and thus easy to share.  You can put the report file out on an Intranet and let users download, or attach to a copy of an E-mail note.  Please note that users may be prompted to refresh links if the  report is a graph or contains embedded graphs.  They should respond no if asked.


Shift Cell Format

There are two macros that modify the cell's format:  One that shifts the format to the left one space at a time, and another that removes this shifting, also one space at a time.  The purpose of these macros is to allow you to line up columns of numbers under a heading without them being centered or flush against the right edge of the cell.  Ie, they add spacing.


Copy Formulas or All But Borders

These two commands do exactly what they say:  they copy jusf formulas or all cell settings except for borders.  The second command, copy all but borders, works only in Excel 7 or higher.  Both commands must first be set to a button in order for you to use. (This is because displaying the command list clears the clipboard)


The Legal Stuff

EVALUATION, AND/OR USE OF THIS SOFTWARE INDICATES YOUR AGREEMENT WITH THE FOLLOWING TERMS AND CONDITIONS.  These terms and conditions shall be construed, interpreted, and governed by the laws of the State of Delaware.

LICENSE AGREEMENT

Under the software license from Macro Systems, purchasing a copy of The Spreadsheet Assistant™ gives you only the right to use the software in only one of the following two ways:  1) The software may be used by a single person who uses the software on an office computer during the day and a home computer during the evening.  Or 2) the software may be dedicated to a single computer used by several individuals in the course of a day.  Copies of the software may be made only in support of these uses.

You may use what you learn from examination of the macro code to develop macros for your own use and for those in your immediate work group.  It does not give you the right to distribute a derivative work beyond this.  Nor does it give you the right to resale or distribute the software or a modified version of the software.  If you wish to distribute a derivative work, a license agreement must first be obtained from Macro Systems.

Copies of the software may be stored ona network server and accessed through the network, provided that you have obtained individual licenses for the software covering all computers that will concurrently access the software through the network and provided that the use is per the above restrictions.

LIABILITY AND WARRANTY

This software is distributed as is and without warranties.  No statement made about this software by distributors, in advertisements, in software reviews, or in the documentation constitutes a warranty.

In no event shall Macro Systems, owner of the copyright, or the author of the program be liable for any loss of profit, any problems or any damage resulting from the use, evaluation, or modification of this software.  Evaluate, use, and modify this program at your own risk. There is no guarantee that this software will operate uninterrupted or error free.  Any liability will be limited exclusively to product replacement or refund of the purchase price.

EXCEPT AS EXPRESSLY PROVIDED HEREIN, THERE ARE NO OTHER WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE WITH RESPECT TO THE SOFTWARE AND ANY ACCOMPANYING WRITTEN MATERIALS.  IN NO EVENT WILL MACRO SYSTEMS BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY LOSS OF PROFITS, LOST SAVINGS, OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF YOUR USE OR INABILITY TO USE THE SOFTWARE, EVEN IF MACRO SYSTEMS OR AN AUTHORIZED REPRESENTATIVE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

This limited warranty gives you specific legal rights.  Some states provide other rights, and some states do not allow excluding or limiting implied warranties or limiting liability for incidental or consequential damages.  As a result, the above limitations and/or exclusions may not apply to you.  Some jurisdictions have statutory consumer protection provisions that may supersede this section of this Agreement.

RETURNS AND REFUNDS

Only un-opened diskette packages can be returned for refund.  If the diskette package has been opened, there will be no refund.  Shipping and handling is non-refundable.  Defective disks will be replaced free of charge if returned within 60 days of purchase to Macro Systems, 1008 Lawndale Road, Wilmington, DE 19810.

COPYRIGHT AND TRADEMARKS

The software files that comprise The Spreadsheet Assistant™ are copyrighted (c) software of Macro Systems.  All rights are reserved.  It is not in the public domain.  It is the property of Macro Systems.  The Spreadsheet Assistant™, ReportRunner™, DataLoader™, and The Spreadsheet Productivity Tools™ are trademarks of Macro Systems.


Other Macro Systems Products

In addition to the Spreadsheet Assistant™, Macro Systems offers the following products for Microsoft Excel:

Microsoft Excel Visual Basic Macros Made Easy™ is the best way to learn Visual Basic for Excel.  This is a set of Windows help files that teaches you how to write Visual Basic macros in Excel.  It is the equivalent of a 750 page book.  It addresses the practical side of creating macros and is one of the best books on Visual Basic macros.  It contains many practical examples of macros.  Quickly search for any topic and jump to it instantly.  Written for both the beginner and the experienced macro writer.  Load on your work and home PC for easy access all the time.

ReportRunner™ is an easy to use menu driven system that prints mulitple Excel reports and graphs automatically.  No more having to go from file to file and remembering what you should print.  ReportRunner™ can automatically open Excel files, print reports, and close files. Multiple reports can be printed just by highlighting the desired reports and selecting print from ReportRunner™'s menu.  ReportRunner™ stores and reloads your report settings so that you don't have to worry about report settings or configure reports!  Report settings are easy to store, update, and modify.  ReportRunner™ can also make certain the starting page numbers are correct from report to report!

DataLoader™ is a menu driven system that makes loading and transferring data from one file or sheet to another extremely easy.  By loading data with DataLoader™, you eliminate manual input errors and get the work done far faster.   Data is loaded based on unique data identification keys such as cells containing an ID number, a social security number, or a combination of identifying cells.  Data that is not loaded can be marked for easy identification and follow-up.  The data can be in any order. DataLoader has many options.


How To Order

If you have access to the Internet, you will be able to download the latest versions of all the programs whenever a new release is made!  If you provide your E-mail address when you place the order, we will send download instructions to you via E-mail, insuring the fastest possible delivery of the software to you.

800 Number And Credit Card Orders

CompuServe Orders For CompuServe Members

Mail Orders

The following are the prices for these great products.  And, if you mail in your order, the prices are even lower!

The SpreadSheet Assistant™	Only	$29.95

The best Excel productivity tools ever!

Microsoft Excel Visual Basic Macros Made Easy™	Now Just	$24.95

Learn to write your own macros!

ReportRunner™ and DataLoader™	Both For	$24.95

Take the pain out of printing reports

and loading data!

------------ OR ORDER --------------------

The Spreadsheet Productivity Tools™	SAVE!!	$49.95

(ALL OF THE ABOVE - A FANTASTIC DEAL!)

SHIPPING AND HANDLING	ZIP, ZERO, NONE!!


800 Number And Credit Card Orders

You can also order with MasterCard, Visa, American Express, or Discover from Public Software Library by calling 800-242-4775 or 713-524-6394.  You can also FAX your order to 713-524-6398 or by E-mail to 71355.470@Compuserve.Com

THE ABOVE PHONE NUMBERS ARE FOR CREDIT CARD ORDERS ONLY.  THE OPERATORS CAN NOT PROVIDE ANY SUPPORT OR INFORMATION ON THESE PRODUCTS, NOR RELAY ANY SUCH REQUEST.

    Any questions about the status of the shipment of the order, refunds, registration options, product details, technical support, volume discounts, dealer pricing, site licenses, etc., should be directed to Robert Flanagan, E-mail address 72774.416@Compuserve.Com or by phone 302-475-8171.  Phone calls should be made between 7-10PM Eastern Standard Time.

Please use the following reference numbers for phone orders.

Number    Use for:

14882     The SpreadSheet Assistant™

14631     Microsoft Excel Visual Basic Macros Made Easy™

11704     ReportRunner™ And DataLoader™

OR

14883     The Spreadsheet Productivity Tools™

          (ALL OF THE ABOVE - A FANTASTIC DEAL!)

Please note that placement of an order indicates your agreement with the terms and conditions of use of the software.


CompuServe Orders For CompuServe Members

If you are a CompuServe member, you may charge your order to your CompuServe accountby typing GO SWREG.  Use the following registration numbers depending on your order.  I check my E-mail daily (except when traveling, which is not to frequent) so that I can fill orders promptly.  You will be sent instructions on how to download the latest version of the files, along with future updates.

Number    Use for:

13210     Registration of The Spreadsheet Assistant™

11383     Registration of Microsoft Excel Visual Basic Macros Made Easy™

13212      Registration of ReportRunner™ and DataLoader™

OR

13211.....Registration of The Spreadsheet Productivity Tools™ - All of the above programs.  SAVE!!

Please note that placement of an order indicates your agreement with the terms and conditions of use of the software.


Mail Orders

     To send a mail order, simply print out the following order form and mail to:

Macro Systems

1008 Lawndale Road

Wilmington, DE 19810

If you provide your E-mail address when you place the order, we will send Internet download instructions to you via E-mail, insuring the fastest possible delivery of the software to you.

Item 	Amount

The SpreadSheet Assistant™	$29.95	________

Microsoft Excel Visual Basic Macros Made Easy™	$24.95	________

ReportRunner™ and DataLoader™	$24.95	________

------------ OR ORDER --------------------

The Spreadsheet Productivity Tools™	$49.95	________

(ALL OF THE ABOVE - A FANTASTIC DEAL!!)

Mail Order Discount - save with mail orders!	$(5.00)

Shipping and Handling 	Zip, Zero, None!

TOTAL ORDER AMOUNT	________

Please note that CREDIT CARD ORDERS CAN NOT BE ACCEPTED VIA MAIL ORDER.  All credit card orders must be placed via PSL.

Name: ________________________________________________________

Address: _____________________________________________________

     _________________________________________________________

City, State, Country: ________________________________________

Zip/Postal Code: _____________________________________________

E-MAIL ADDRESS _______________________________________________


The Menu Additions

When you startup Excel for the first time after doing the above, ASSISTNT.XLS will be loaded automatically.  It adds a number of new menu commands to the pull-down and pop-up menus.  The command "Spreadsheet Assistant" found under the Tools menu, gives you access to the Spreadsheet Assistant command list.  The new  menu commands all have a "»" symbol in front of them.  You will also see a new menu labeled "Quik" that gives you additional commands.  If you don't want the commands added to the menus, use the command "Show or Remove Spreadsheet Assistant Commands on the Excel Menus" found near the bottom of the command list to remove the new commands.  You can use this command to add the new commands back whenever you want.  Or, you can follow the directions in the help file on how to customize the Spreadsheet Assistant and add your own set of customized menu commands.  The "Spreadsheet Assistant" menu item under the Tools menu can not be removed.


Using The Menu Editor To Customize The Excel Menus

You can use Microsoft Excel's menu editor to customize the Excel menus with The Spreadsheet Assistant's commands that you want to appear on the menus.  It takes a bit of work to do this, but you will find it well worth it.  To customize the menus do the following:

STEP 1 - Select "Display Spreadsheet Assistant Macro Command Name" for the commands you want to add to the menu.  This command is near the bottom of command list.  Make note of the macro names, which begin with "Z_".  This naming convention is used so that the Spreadsheet Assistants macros appear below any macros you may have.

STEP 2 - remove the menu additions that The Spreadsheet Editor adds.  Do this by displaying the Spreadsheet Assistant command menu and running "Show or Remove Spreadsheet Assistant Commands On the Excel Menus".  This command is also near the bottom of the command list.  Unclick all the boxes and select OK. This will automatically save ASSISTNT.XLS to make these changes permanent.

STEP 3 - Unhide ASSISTNT.XLS and go to any module sheet.  The un-registered shareware version can not be unhidden, and thus you can not customize the menus until you register and received the non-shareware version.

STEP 4 - Select TOOLS, MENU EDITOR from the Excel Menus.

STEP 5 - In the menus bars dropdown list, select the menu you want to modify. In the menu box, select the menu you want to modify.  In the menu item box select the location above which you wish to add a new menu item.  Click on INSERT to open a spot on the menu for a new menu item.

STEP 6 - In the Caption box, which is now active, type in the words you want to appear on the menu.  In the macro box, select the macro that will run when the menu item is selected. (Which you found by doing step one.)  If you want a separator bar to appear, put a single dash ("-") in the caption box, and do not specify a macro

Step 7 - Select OK to exit the Menu Editor.  Then from the Spreadsheet Assistant command menu, select "Save The Spreadsheet 'Assistant Settings".  It is near the bottom of the list.  This will save ASSISTNT.XLS, making the menu changes permanent.  Please note that the menu changes appear only when ASSISTNT.XLS is open.


Glossary

A

As each file is saved

C

Casade Screens

F

F8 then allows extension

Format to 0

G

Go to the File Manager

I

Insert Horizontal Page Breaks Every X Rows

L

Lots of Options

R

Remove the Print Area, Titles, Page Breaks...

Z

Zoom to 85%


As each file is saved

If saving the file will result in replacing a file by the same name then Excel's pop up menu to confirm this is displayed for your decision on what to do.


Casade Screens

Although this is one of Excel's selections, I've located it on the Windows pull down menu as opposed to where Excel has it located, which requires more mouse clicking.


F8 then allows extension

The F8 key is actually a Microsoft Excel programed key.  It allows you to extend the select range to where ever you click until you either press the F8 key again, press the ESC key or do an Excel action.  SO, after you use it to select the range, press the ESC key.


Format to 0

This command not only formats the cell to the "0" format, it also adds commas between thousands.


Go to the File Manager

This command in addition to being on the macro list is also on the File pull down menu.  Use the Windows file manager to open and close files when you want to see  a bigger list with more information.

    And, you can use the file manager to rename, move, copy, etc.  Bob's Macros won't take credit for these features, just the convenience of getting to the  file manager.


Insert Horizontal Page Breaks Every X Rows

One trick you can do here!  When the box pops up to ask for the number of rows, you can enter a formula.  For example 33-7.  Why, well if the next break is due on line 33 starting from line 7, it makes it easier


Lots of Options

You can add text to cells containing text or only to cells without text.


Remove the Print Area, Titles, Page Breaks...

If you don't like to see the print area, have put in too many manual page breaks, etc, this command offers you a way to get a fresh start!


Zoom to 85%

This command is most useful if you have a 17 inch or larger monitor.  The 85% zoom setting gives you a little bit more on the screen without impacting the readability of the screen